using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PHSRAG.Insight21.FormsManagement;
using PHSRAG.Insight21.Policy;

namespace InsightMigrationUtility
{
    public class DBAccess
    {
        #region private members
        private SqlConnection connection;
        private string username;
        private string password;
        private string server;
        #endregion

        #region Public Properties
        public string Username
        {
            get { return username; }
            set { username = value; }
        }

        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        public string Server
        {
            get { return server; }
            set { server = value; }
        }

        public SqlConnection SqlConnection
        {
            get { return connection; }
        }

        public string ConnectionString
        {
            get
            {
                // We don't set the database until it's selected, so generally Database won't be in the connection string
                if (SqlConnection.ConnectionString.IndexOf("Database") == -1)
                {
                    return string.Format("{0};Database={1}", SqlConnection.ConnectionString, SqlConnection.Database);
                }
                else
                    return SqlConnection.ConnectionString;
            }
        }
        #endregion

        public DBAccess()
        {
        }

        public ArrayList GetDBList()
        {
            if (connection == null)
                GetConnection();

            ArrayList dbList = new ArrayList();

            connection.Open();
            SqlCommand sqlCommand = new SqlCommand("sp_helpdb", connection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                dbList.Add(reader["name"].ToString());
            }
            reader.Close();

            return dbList;
        }

        public ArrayList GetQuestionnaires( int moduleType )
        {
            ArrayList questionnaireList = new ArrayList();
            SqlCommand command = new SqlCommand( "GetQuestionnaires", connection );
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter( "@moduleType", SqlDbType.Int );
            param.Value = moduleType;
            command.Parameters.Add( param );
            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Questionnaire questionnaire = new Questionnaire(new NullCachePolicy( ), row, this);
                    questionnaireList.Add(questionnaire);
                }
            }

            return questionnaireList;
        }

        private void GetConnection()
        {
            connection = new SqlConnection(string.Format("Persist Security Info=true;Server={2};User ID={0};Password={1};Trusted_Connection=False;", Username, Password, Server));
        }
    }
}
